﻿namespace etracks.Web.Controllers
{
    using etracks.EntityModels;
    using etracks.Helpers;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Web.Mvc.Ajax;
    using System.Data.Linq;
    using System.Data.Common;

	[Authorize]
	public class IssueController : BaseController
	{
		public ActionResult Index ()
		{
            IssueIndexViewModel vm = new IssueIndexViewModel();

            vm.Issues = IssueController.GetIssueCount();

			return View (vm);
		}

        public ActionResult List()
        {
            IssueListViewModel vm = new IssueListViewModel();

            vm.IssueList = IssueController.GetIssueList();

            return this.List(vm);
        }

        public ActionResult View(int id)
        {
            IssueReadOnlyViewModel vm = GetReadOnlyIssue(id);

            return View(vm);
        }

        public ActionResult Add()
        {
            IssueRegistrationViewModel vm = new IssueRegistrationViewModel();

            vm.ProjectList = IssueController.GetProjectList();

            return View(vm);
        }

        [HttpPost]
        public ActionResult Add(IssueRegistrationViewModel vm)
        {
            try
            {
                Dictionary<string, object> parameters = new Dictionary<string, object>();
                parameters.Add("name", vm.Name);
                parameters.Add("description", vm.Description);
                parameters.Add("project_id", vm.ProjectId);

                PostgresHelper.Execute("INSERT INTO issue (name, description, project_id) VALUES (:name, :description, :project_id);", parameters);

                this.SuccessAlert("Issue added.");
            }
            catch (Exception)
            {
                throw;
            }

            vm.ProjectList = IssueController.GetProjectList();

            return View(vm);
        }

        public ActionResult Edit(int id)
        {
            IssueRegistrationViewModel vm = new IssueRegistrationViewModel();

            Issue p = GetIssue(id);

            if (p != null)
            {
                vm = new IssueRegistrationViewModel
                {
                    Id= p.Id,
                    Name = p.Name, 
                    Description = p.Description,
                    ProjectId = p.ProjectId
                };
            }

            vm.ProjectList = IssueController.GetProjectList();

            return View(vm);
        }

        [HttpPost]
        public ActionResult Edit(IssueRegistrationViewModel vm)
        {
            try
            {
                Dictionary<string, object> parameters = new Dictionary<string,object>();
                parameters.Add("id", vm.Id);
                parameters.Add("name", vm.Name);
                parameters.Add("description", vm.Description);
                parameters.Add("project_id", vm.ProjectId);

                PostgresHelper.Execute("UPDATE issue SET name = :name, description = :description, project_id = :project_id WHERE id = :id;", parameters);

                this.SuccessAlert("Issue updated.");
            }
            catch (Exception)
            {
                throw;
            }

            vm.ProjectList = IssueController.GetProjectList();

            return View(vm);
        }


        [HttpPost]
        public ActionResult Delete(int id)
        {
            try
            {
                Dictionary<string, object> parameters = new Dictionary<string, object>();
                parameters.Add("id", id);

                PostgresHelper.Execute("DELETE FROM issue WHERE id = :id;", parameters);

                this.SuccessAlert("Issue deleted.");
            }
            catch (Exception)
            {
                throw;
            }

            return RedirectToAction("List");
        }

        private ActionResult List(IssueListViewModel vm)
        {
            return View(vm);
        }

        public ActionResult Search()
        {
            return RedirectToAction("List");
        }

        [HttpPost]
        public ActionResult Search(string search_term)
        {
            IssueSearchViewModel vm = new IssueSearchViewModel();

            vm.Search_Term = search_term;

            vm.IssueList = IssueController.IssueSearchResultsList(search_term);

            return View(vm);
        }

        public ActionResult Registration()
        {
            return View(new etracks.Web.IssueRegistrationViewModel());
        }

        [HttpPost]
        public ActionResult Registration(IssueRegistrationViewModel vm)
        {
            // Validate vm
            if (string.IsNullOrEmpty(vm.Name))
                ModelState.AddModelError("Name", "Cannot be empty.");

            if (ModelState.IsValid)
            {
                // Add student into list database records.
                string new_id = RegisterIssue(vm.Name);
            }

            return View(vm);
        }

        ////////////////////////////////////////////////////////////////////////////////
        // Data retrieval hack functions
        private static int GetIssueCount()
        {
            int result = 0;
            object db_result = PostgresHelper.GetObject("SELECT COUNT(*) FROM Issue;");

            if (db_result != null)
                int.TryParse(db_result.ToString(), out result);
            
            return result;
        }

        private static Issue GetIssue(int id)
        {
            //List<Issue> result = new List<Issue>();
            Issue result = new Issue();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("id", id);

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM Issue WHERE id = :id ORDER BY id LIMIT 1;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Issue
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Description = n.Field<string>("description"),
                    ProjectId = n.Field<int?>("project_id")
                }).FirstOrDefault();
            }

            return result;
        }

        private static IssueReadOnlyViewModel GetReadOnlyIssue(int id)
        {
            IssueReadOnlyViewModel result = new IssueReadOnlyViewModel();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("id", id);

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT  i.*, p.name AS ""project_name""
FROM    issue i
LEFT OUTER JOIN
        project p
        ON i.project_id = p.id
WHERE   i.id = :id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new IssueReadOnlyViewModel
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Description = n.Field<string>("description"),
                    ProjectId = n.Field<int?>("project_id"),
                    ProjectName = n.Field<string>("project_name")
                }).FirstOrDefault();
            }

            return result;
        }

        private static List<IssueReadOnlyViewModel> GetIssueList()
        {
            List<IssueReadOnlyViewModel> result = new List<IssueReadOnlyViewModel>();

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT  i.*, p.name AS ""project_name"" 
FROM    issue i
LEFT OUTER JOIN 
        project p
        ON i.project_id = p.id
ORDER BY id;");

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new IssueReadOnlyViewModel
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    ProjectId = n.Field<int?>("project_id"),
                    ProjectName = n.Field<string>("project_name")
                }).ToList();
            }

            return result;
        }

        private static List<Issue> IssueSearchResultsList(string search_term)
        {
            List<Issue> result = new List<Issue>();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            
            parameters.Add("search_term", string.Format("%{0}%", search_term));

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT  p.*, pt.name AS ""Issue_type_name""
FROM    Issue p
LEFT OUTER JOIN
        Issue_type pt
        ON p.Issue_type_id = pt.id
WHERE   lower(p.name) LIKE lower(:search_term) 
        OR lower(p.alias) LIKE lower(:search_term)
        OR lower(pt.name) LIKE lower(:search_term)
ORDER BY id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Issue
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                }).ToList();
            }

            return result;
        }

        private static string RegisterIssue(string name)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("name", name);
            
            string sql = "INSERT INTO Issue (name) VALUES (:name) RETURNING id;";

            object db_result = PostgresHelper.GetObject(sql, parameters);

            if (db_result != null)
                return db_result.ToString();
            else
                return null;

        }

        private static List<Project> GetProjectList()
        {
            List<Project> result = new List<Project>();

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM project ORDER BY id;");

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Project
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias"),
                    ProjectTypeId = n.Field<int?>("project_type_id")
                }).ToList();
            }

            return result;
        }

	}
}
